// ----------------------------------------------------------------------------
//
// IDataStore Interface implementation for SQL Server.
//
// ----------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;

namespace WERServices
{
    /// <summary>
    ///  IDataStore Interface implementation for SQL Server.
    /// </summary>
    public class SqlServerDataStore : IDataStore
    {
        #region Fields
        private List<SqlParameter> parameterList = new List<SqlParameter>();
        #endregion Fields

        #region Properties
        #endregion Properties

        #region Methods
        /// <summary>
        /// Method to get the SqlDbType from the string type input.
        /// </summary>
        /// <param name="type">The type to intepret into a valid SqlDbType.</param>
        /// <returns>The SqlDbType value.</returns>
        private SqlDbType GetSqlDbType(string type)
        {
            if (string.IsNullOrEmpty(type) == true)
            {
                throw new ArgumentNullException("type", "type cannot be null or empty.");
            }

            SqlDbType sqlDbType = SqlDbType.NVarChar;
            switch (type)
            {
                case "int":
                    sqlDbType = SqlDbType.Int;
                    break;
                case "nvarchar":
                    sqlDbType = SqlDbType.NVarChar;
                    break;
                case "smalldatetime":
                    sqlDbType = SqlDbType.SmallDateTime;
                    break;
                case "datetime":
                    sqlDbType = SqlDbType.DateTime;
                    break;
                case "char":
                    sqlDbType = SqlDbType.Char;
                    break;
                case "bigint":
                    sqlDbType = SqlDbType.BigInt;
                    break;
                case "varchar":
                    sqlDbType = SqlDbType.VarChar;
                    break;
                case "bit":
                    sqlDbType = SqlDbType.Bit;
                    break;
                default:
                    break;
            }

            return sqlDbType;
        }

        /// <summary>
        /// Gets the initialized connection to SQL Server. If the connection 
        /// is not instantiated, it will be instantiated and then returned.
        /// It will use the ConnectionString field to get the connection
        /// criteria.
        /// </summary>
        /// <param name="connectionString">Connection string to the database.</param>
        /// <returns>SqlConnection object.</returns>
        private SqlConnection GetConnection(string connectionString)
        {
            if (String.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException
                    (
                        "connectionString"
                        , "Connection string cannot be null or empty."
                    );
            }
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// Builds a SqlCommand object using standard parameters.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure to execute.</param>
        /// <param name="connectionString">Connection string to the database.</param>
        /// <returns>SqlComnmand object.</returns>
        private SqlCommand GetCommand(string storedProcedureName, string connectionString)
        {
            if (String.IsNullOrEmpty(storedProcedureName))
            {
                throw new ArgumentNullException
                    (
                        "storedProcedureName"
                        , "Stored procedue name cannot be null or empty."
                    );
            }

            SqlCommand sqlCommand = new SqlCommand
                (
                    storedProcedureName
                    , GetConnection(connectionString)
                );
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddRange(parameterList.ToArray());
            return sqlCommand;
        }
        #endregion Methods

        #region IDataStore Members
        /// <summary>
        /// Method to add a stored procedure parameter to the parameter list.
        /// </summary>
        /// <param name="parameterName">Name of the paramerer.</param>
        /// <param name="type">Data type of the parameter.</param>
        /// <param name="size">Size of the parameter.</param>
        /// <param name="value">Value of the parameter.</param>
        /// <param name="clearList">Boolean to decide whether to clear the parameter list before adding the parameter.</param>
        public void AddParameter(string parameterName, string type, int size, object value, bool clearList)
        {
            if (clearList == true)
            {
                parameterList.Clear();
            }

            //
            // convert the type to a SqlDbType.
            //
            SqlDbType sqlDbType = this.GetSqlDbType(type);

            SqlParameter sqlParameter = null;

            //
            // check if parameter already exists.
            //
            foreach (SqlParameter p in parameterList)
            {
                if (p.ParameterName == parameterName)
                {
                    sqlParameter = p;
                    break;
                }
            }

            //
            // not found, create new sql parameter.
            //
            if (sqlParameter == null)
            {
                sqlParameter = new SqlParameter();
            }


            //
            //
            // add the name, type and value to the sql parameter.
            sqlParameter.ParameterName = parameterName;
            sqlParameter.SqlDbType = sqlDbType;
            if (size != 0)
            {
                sqlParameter.Size = size;
            }
            sqlParameter.Value = value;

            //
            // add the parameter to the parameter list.
            //
            parameterList.Add(sqlParameter);

        }

        /// <summary>
        /// Method to save the data to the database.
        /// </summary>
        /// <param name="commandName">Name of the object to call (stored procedure name) to store the data.</param>
        /// <param name="connectionString">Connection string to the database.</param>
        public void SaveData(string commandName, string connectionString)
        {
            //
            // get the command object
            //
            SqlCommand sqlCommand = this.GetCommand(commandName, connectionString);

            try
            {
                //
                // open the connection
                //
                sqlCommand.Connection.Open();

                //
                // execute the stored procedure.
                //
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //
                // populate the exception with additional details
                // from the sql command.
                //
                PopulateException(ex, sqlCommand);

                //
                // rethrow the exception
                //
                throw;
            }
            finally
            {
                //
                // close and dispose the connection.
                //
                sqlCommand.Connection.Close();
                sqlCommand.Connection.Dispose();
            }
        }

        /// <summary>
        /// Method to read the data out of the database.
        /// </summary>
        /// <param name="commandName">Name of the object to call (stored procedure name) to read the data from the database.</param>
        /// <param name="connectionString">Connection string to the database.</param>
        /// <returns>Returns a DataSet object containing the data.</returns>
        public DataSet ReadData(string commandName, string connectionString)
        {
            //
            // declare the SqlDataAdapter and DataSet objects.
            //
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            DataSet dataSet = new DataSet("ReadDataSet");

            //
            // get the command object
            //
            SqlCommand sqlCommand = this.GetCommand(commandName, connectionString);

            try
            {
                //
                // open the connecton
                //
                sqlCommand.Connection.Open();

                //
                // set the command for the data adapter to the SqlCommand object.
                //
                sqlDataAdapter.SelectCommand = sqlCommand;

                //
                // Fill the data set
                //
                sqlDataAdapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                //
                // populate the exception with additional details
                // from the sql command.
                //
                PopulateException(ex, sqlCommand);

                //
                // rethrow the exception
                //
                throw;
            }
            finally
            {
                //
                // close and dispose the connection.
                //
                sqlCommand.Connection.Close();
                sqlCommand.Connection.Dispose();
            }

            return dataSet;
        }

        /// <summary>
        /// Helper method to populate the exception details.
        /// </summary>
        /// <param name="ex">Exception object.</param>
        /// <param name="cmd">SqlCommand object to get the parameter list from.</param>
        private void PopulateException(Exception ex, SqlCommand cmd)
        {
            if (cmd.CommandType == CommandType.StoredProcedure)
            {
                ex.Data["StoredProcedureName"] = cmd.CommandText;
            }
            else
            {
                ex.Data["SqlText"] = cmd.CommandText;
            }

            foreach (SqlParameter parameter in cmd.Parameters)
            {
                ex.Data[parameter.ParameterName] =
                    parameter.Value == null
                        ? "NULL"
                        : parameter.Value.ToString();
            }
        }
        #endregion
    }
}